package com.trh.dictionary.service.oracleservice;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.trh.dictionary.bean.ColumnInfo;
import com.trh.dictionary.bean.DO.*;
import com.trh.dictionary.bean.IndexInfo;
import com.trh.dictionary.bean.TableInfo;
import com.trh.dictionary.util.Utils;

import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * @author: lixin
 * @program: database-dictionary
 * @description: 创建oracle链接
 * @create: 2019-09-11 11:00
 **/
public class OracleDatabase {
    //驱动
    private static String driver = "oracle.jdbc.driver.OracleDriver";

    //得到表信息
    public static List<TableInfo> getTableInfo(String url, String username, String password) throws Exception {

        Map<String, Index> indexNameMap = Maps.newHashMap();

        Map<String, Column> columnMap = Maps.newHashMap();

        Map<String, IndexNormal> indexNormalMap = Maps.newHashMap();

        List<TableInfo> list = Lists.newArrayList();
        Connection conn =null;
        try {
            // 1.注册驱动
            Class.forName(driver);
            // 2.建立连接
            conn = DriverManager.getConnection(url, username, password);

            // 1.获取所有表的名称
            PreparedStatement pstmt = conn.prepareStatement("select t.table_name,u.COMMENTS from user_tables t LEFT JOIN user_tab_comments u ON t.Table_Name=u.Table_Name");
            ResultSet rs  = pstmt.executeQuery();


            // 2.获取唯一索引以及对应的列名,key为表名,value为主键索引和列名
            PreparedStatement constraintNamePstmt = conn.prepareStatement("select t1.table_name,t1.index_name,t1.COLUMN_NAME  from user_ind_columns t1,user_indexes t2 where t1.INDEX_NAME=t2.index_name and t2.uniqueness= 'UNIQUE'");
            ResultSet indexNameRs = constraintNamePstmt.executeQuery();
            while (indexNameRs.next()) {
                // 表名
                String tableName = indexNameRs.getString("TABLE_NAME");
                // 索引名
                String constraintName = indexNameRs.getString("INDEX_NAME");
                // 列名
                String columnName = indexNameRs.getString("COLUMN_NAME");
                if (Utils.isNotNull(indexNameMap.get(tableName))) {
                    Index index2 = indexNameMap.get(tableName);
                    index2.getColumnNameList().add(columnName);
                    indexNameMap.put(tableName, index2);
                } else {
                    Index index1 = new Index();
                    List<String> columnNameList = Lists.newArrayList();
                    index1.setTableName(tableName);
                    index1.setConstraintName(constraintName);
                    columnNameList.add(columnName);
                    index1.setColumnNameList(columnNameList);
                    indexNameMap.put(tableName, index1);
                }
            }



            // 3.获取所有列数据 key为表名 value为列数据
            PreparedStatement columnIdPstmt = conn.prepareStatement("SELECT  T.TABLE_NAME,T.column_id,T.column_name,T.data_type,T.data_default,T.nullable,b.comments FROM USER_TAB_COLUMNS T LEFT JOIN user_col_comments b ON T.TABLE_NAME =b.table_name AND T.COLUMN_NAME =b.column_name");
            ResultSet columnRs = columnIdPstmt.executeQuery();
            while (columnRs.next()) {
                String tableName = columnRs.getString("TABLE_NAME");
                String columnId = columnRs.getString("COLUMN_ID");
                String columnName = columnRs.getString("COLUMN_NAME");
                String dataType = columnRs.getString("DATA_TYPE");
                String dataDefault = columnRs.getString("DATA_DEFAULT");
                String nullable = columnRs.getString("NULLABLE");
                String comments = columnRs.getString("COMMENTS");
                if (Utils.isNotNull(columnMap.get(tableName))) {
                    Column column = columnMap.get(tableName);
                    ColumnDetail columnDetail = new ColumnDetail();
                    columnDetail.setColumnId(columnId);
                    columnDetail.setColumnName(columnName);
                    columnDetail.setDataType(dataType);
                    columnDetail.setDataDefault(dataDefault);
                    columnDetail.setNullable(nullable);
                    columnDetail.setComments(comments);
                    column.getColumnDetailList().add(columnDetail);
                    columnMap.put(tableName, column);
                } else {
                    Column column = new Column();
                    column.setTableName(tableName);
                    ColumnDetail columnDetail = new ColumnDetail();
                    columnDetail.setColumnId(columnId);
                    columnDetail.setColumnName(columnName);
                    columnDetail.setDataType(dataType);
                    columnDetail.setDataDefault(dataDefault);
                    columnDetail.setNullable(nullable);
                    columnDetail.setComments(comments);
                    List<ColumnDetail> columnDetails = Lists.newArrayList();
                    columnDetails.add(columnDetail);
                    column.setColumnDetailList(columnDetails);
                    columnMap.put(tableName, column);
                }
            }

            // 4.获取所有的的索引说明 key为表名 value为索引说明
            PreparedStatement rownumPstmt = conn.prepareStatement("SELECT rownum,rs.* FROM ( select i.table_name,t.index_name, decode(i.uniqueness,'UNIQUE','UNIQUE',i.index_type) index_type ,listagg(t.column_name,',') within group (order by t.index_name) col_name from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name  GROUP BY t.index_name, i.index_type,i.table_name,i.uniqueness) rs ");
            ResultSet indexNormalRs = rownumPstmt.executeQuery();
            while (indexNormalRs.next()) {
                //
                String tableName = indexNormalRs.getString("TABLE_NAME");
                //
                String rownum = indexNormalRs.getString("ROWNUM");
                //
                String indexName = indexNormalRs.getString("INDEX_NAME");
                //
                String indexType = indexNormalRs.getString("INDEX_TYPE");
                //
                String colName = indexNormalRs.getString("COL_NAME");
                if (Utils.isNotNull(indexNormalMap.get(tableName))) {
                    IndexNormal indexNormal = indexNormalMap.get(tableName);
                    IndexNormalDetail indexNormalDetail = new IndexNormalDetail();
                    indexNormalDetail.setRownum(rownum);
                    indexNormalDetail.setIndexName(indexName);
                    indexNormalDetail.setIndexType(indexType);
                    indexNormalDetail.setColName(colName);
                    indexNormal.getIndexNormalDetails().add(indexNormalDetail);
                    indexNormalMap.put(tableName, indexNormal);
                } else {
                    IndexNormal indexNormal = new IndexNormal();
                    indexNormal.setTableName(tableName);
                    IndexNormalDetail indexNormalDetail = new IndexNormalDetail();
                    indexNormalDetail.setRownum(rownum);
                    indexNormalDetail.setIndexName(indexName);
                    indexNormalDetail.setIndexType(indexType);
                    indexNormalDetail.setColName(colName);
                    List<IndexNormalDetail> indexNormalDetails = Lists.newArrayList();
                    indexNormalDetails.add(indexNormalDetail);
                    indexNormal.setIndexNormalDetails(indexNormalDetails);
                    indexNormalMap.put(tableName, indexNormal);
                }
            }

            while (rs.next()) {

                TableInfo tableInfo = new TableInfo();

                //得到表名
                String tableName = rs.getString("TABLE_NAME");

                // 得到主键索引(可以有多个)
                Index index = indexNameMap.get(tableName);


                String indexName="";
                List<String> columnNameList=Lists.newArrayList();
                if(Utils.isNotNull(index)){
                    // 索引名称
                     indexName = index.getConstraintName();
                    // 索引列名
                    columnNameList = index.getColumnNameList();
                }



                // 通过表名查询所有的列数据
                List<ColumnInfo> listColumn = Lists.newArrayList();
                Column column = columnMap.get(tableName);
                for(ColumnDetail columnDetail:column.getColumnDetailList()){
                    ColumnInfo columnInfo = new ColumnInfo();
                    // 列名
                    columnInfo.setName(columnDetail.getColumnName());
                    // 是否为主键
                    columnInfo.setIsIndex(0);
                    if(columnNameList.contains(columnDetail.getColumnName())){
                        columnInfo.setIsIndex(1);
                    }
                    // 序号
                    columnInfo.setOrder(Integer.parseInt(columnDetail.getColumnId()));
                    // 类型
                    columnInfo.setType(columnDetail.getDataType());
                    // 默认值
                    columnInfo.setDefaultValue(columnDetail.getDataDefault());
                    // 是否为空
                    columnInfo.setIsNull(columnDetail.getNullable());
                    // 备注
                    columnInfo.setDescription(columnDetail.getComments());
                    listColumn.add(columnInfo);
                }

                // 通过表名查询普通索引
                List<IndexInfo> listIndex = Lists.newArrayList();
                IndexNormal indexNormal = indexNormalMap.get(tableName);
                if(Utils.isNotNull(indexNormal)) {
                    for (IndexNormalDetail indexNormalDetail : indexNormal.getIndexNormalDetails()) {
                        IndexInfo indexInfo = new IndexInfo();

                        if (indexName.equalsIgnoreCase(indexNormalDetail.getIndexName())) {
                            indexInfo.setIsIndex(1);
                        }

                        indexInfo.setOrder(Integer.parseInt(indexNormalDetail.getRownum()));
                        indexInfo.setName(indexNormalDetail.getIndexName());
                        indexInfo.setType(indexNormalDetail.getIndexType());
                        indexInfo.setContainKey(indexNormalDetail.getColName());
                        listIndex.add(indexInfo);
                    }
                }

                tableInfo.setTableName(tableName);
                tableInfo.setDescription(rs.getString("COMMENTS"));
                tableInfo.setColumnList(listColumn);
                tableInfo.setIndexInfoList(listIndex);
                list.add(tableInfo);

            }

            rs.close();
            indexNameRs.close();
            columnRs.close();
            indexNormalRs.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            if (conn != null) {
                try {
                    //关闭链接
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return list;
    }






}